drop table jms_dm.dm_tab_scan_piece_summary_dt;
CREATE TABLE jms_dm.dm_tab_scan_piece_summary_dt
(
  `format_scan_time` datetime NULL COMMENT '格式化日期',
  `scan_user_code` varchar(50) NULL COMMENT '扫描员code',
  `organization_code` varchar(50) NULL COMMENT '中心组织code',
  `supplier_id` varchar(50) NULL COMMENT '供应商id',
  `scan_type_code` varchar(50) NULL COMMENT '扫描类型code 1：发件 2：到件 3：拆包',
  `network_code` varchar(50) NULL COMMENT '网点code',
  `organization_name` varchar(200) NULL COMMENT '中心组织name',
  `supplier_code` varchar(50) NULL COMMENT '供应商编码',
  `supplier_name` varchar(300) NULL COMMENT '供应商名',
  `scan_type` varchar(50) NULL COMMENT '扫描类型',
  `network_name` varchar(200) NULL COMMENT '网点名',
  `network_type` tinyint(4) NULL COMMENT '网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
  `pre_site_code` varchar(50) NULL COMMENT '上一站code',
  `pre_site_name` varchar(200) NULL COMMENT '上一站',
  `next_site_code` varchar(50) NULL COMMENT '下一站code',
  `next_site_name` varchar(200) NULL COMMENT '下一站',
  `shipment_no` varchar(50) NULL COMMENT '任务号',
  `shipment_name` varchar(300) NULL COMMENT '任务名称',
  `scan_user` varchar(50) NULL COMMENT '扫描员',
  `scan_user_id` varchar(50) NULL COMMENT '扫描员id',
  `scan_user_type` varchar(200) NULL COMMENT '扫描员员工属性1临时工/2正式工/3小时工(已废弃)/4职能/5环节外包',
  `parts_cnt` bigint(20) NULL COMMENT '散件件数',
  `packages_cnt` bigint(20) NULL COMMENT '包件数',
  `in_packages_cnt` bigint(20) NULL COMMENT '包内件数',
  `scan_cnt` bigint(20) NULL COMMENT '扫描件数',
  `weight` decimal64(10, 2) NULL COMMENT '重量',
  `hours` varchar(15) NULL COMMENT '小时',
  `date_time` date NULL COMMENT 'sr分区',
  `hours_range` varchar(50) NULL COMMENT '小时区间'
) ENGINE = OLAP DUPLICATE KEY( `format_scan_time`, `scan_user_code`, `organization_code`,`supplier_id` )
COMMENT '计件统计'
PARTITION BY RANGE(`date_time`)
(START ('2023-09-01') END ('2023-09-30') EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`network_code`) BUCKETS 3
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-190",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "3",
"in_memory" = "false",
"storage_format" = "V2"
);